package com.aischool.service;

import java.util.ArrayList;
import java.util.List;

import com.aischool.params.AiNotesParams;
import com.aischool.utils.StateEnum;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class AiNotesService {

	public Record getOne(int note_id) {
		return Db.findFirst("SELECT a.*, b.type_name, u.user_name\r\n" + 
				"FROM ai_note a \r\n" + 
				"INNER JOIN ai_note_type b ON b.type_id = a.type_id \r\n" + 
				"INNER JOIN sys_user u ON u.user_id = a.add_userid \r\n" + 
				"WHERE a.note_id = ?", note_id);
	}
	
	public Page<Record> getPage(AiNotesParams params){
		String sql_select = "SELECT a.*, b.type_name ";
		String sql_from   =	"FROM ai_note a "
				+ "INNER JOIN ai_note_type b ON b.type_id = a.type_id ";
		String sql_where  =	"WHERE a.note_state >= 0 ";
		String sql_order = " ORDER BY a.note_id DESC";
		
		int idx = 0;
		
		List<Object> listObj = new ArrayList<Object>();
		if (params.getState() != StateEnum.ALL.getState()) {
			sql_where = "WHERE a.note_state = ? ";
			listObj.add(params.getState());
		}

		
		if (params.getTypeId()>0) {
			sql_where += " AND a.type_id = ? ";
			listObj.add(params.getTypeId());
		}
		
		if (params.getOrgId()>0) {
			sql_where += " AND a.org_id = ? ";
			listObj.add(params.getOrgId());
		}
		
		if (params.getClassId()>0) {
			sql_from += "INNER JOIN (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS t "
					+ "ON CAST(t.ids AS INTEGER) = a.class_id ";
			String ids = "0," + params.getClassId();
			
			listObj.add(idx, ids);
			idx++;
		}
		
		return Db.paginate(params.getPageNo(), params.getPageSize(), sql_select, sql_from + sql_where + sql_order, listObj.toArray());
	
	}
}
